var express = require('express');
var router = express.Router();
var sqlite3 = require('sqlite3').verbose();
var path = require('path');
/* GET users listing. */
router.get('/', function(req, res, next) {
    var page = req.query.page;
    var limit = req.query.limit;
    var db=new sqlite3.Database(path.join(__dirname, "../db/LH.s3db"));
    try{
        sqlStr = "select * from(SELECT \
            s_id,\
            s_activity.[a_no] ,\
            a_name , \
            manager.[m_id] ,\
            manager.[m_name] ,\
            type,\
            s_activity.[status],\
            s_time, \
            e_time ,  \
            s_activity.[time] , \
            score \
          FROM \
            (s_activity JOIN \
            activity on s_activity.[a_no] = activity.[a_no])  join manager on activity.[m_id]=manager.[m_id]) where s_id= ? ";
        if(req.query.searchParams==undefined||req.query.searchParams=='"0"'){
            if(req.query.function=="barchart"){
                db.all(sqlStr,[req.session.username],function(err,rows){
                if(rows.length==0){
                    req.send('0');
                }else{
                    var month = new Date().getMonth()+1;
                    var year = new Date().getFullYear();
                    table={9:0,10:0,11:0,12:0,1:0,2:0,3:0,4:0,5:0,6:0,7:0,8:0};
                    console.log(rows)
                    if(month>=9){
                        //今年9月之后
                        for(var i=0;i<rows.length;i++){
                            var a_year = rows[i].time.split('/')[0];
                            var a_month = rows[i].time.split('/')[1];
                            if(a_year==year&&a_month>=9&&rows[i].score!=-1){
                                table[a_month]=table[a_month]+rows[i].score;
                            }
                        }
                    }else{
                        //去年＋今年
                        for(var i=0;i<rows.length;i++){
                            var a_year = rows[i].time.split('/')[0];
                            var a_month = rows[i].time.split('/')[1];
                            if(a_year==(year-1)&&a_month>=9&&rows[i].score!=-1){
                                table[a_month]=table[a_month]+rows[i].score;
                            }else if(a_year==year&&rows[i].score!=-1)
                                table[a_month]=table[a_month]+rows[i].score;
                        }
                    }
                    console.log(table);
                    res.status(200).json(table);
                    return ;
                }
                } );
            }
            else if(req.query.function=="piechart"){
                db.all(sqlStr,[req.session.username],function(err,rows){
                    if(rows.length==0){
                        req.send('0');
                    }else{
                        table={"智育":0,"美育":0,"德育":0,"体育":0,"劳育":0}
                        var month = new Date().getMonth()+1;
                        var year = new Date().getFullYear();
                        if(month>=9){
                            //今年9月之后
                            for(var i=0;i<rows.length;i++){
                                var a_year = rows[i].time.split('/')[0];
                                var a_month = rows[i].time.split('/')[1];
                                if(a_year==year&&a_month>=9&&rows[i].score!=-1){
                                    table[rows[i].type]+=rows[i].score;
                                }
                            }
                        }else{
                            //去年＋今年
                            for(var i=0;i<rows.length;i++){
                                var a_year = rows[i].time.split('/')[0];
                                var a_month = rows[i].time.split('/')[1];
                                if(a_year==(year-1)&&a_month>=9&&rows[i].score!=-1){
                                    table[rows[i].type]+=rows[i].score;
                                }else if(a_year==year&&rows[i].score!=-1)
                                    table[rows[i].type]+=rows[i].score;
                            }
                        }
                        console.log(table);
                        res.status(200).json(table);
                        return ;
                    }})
            }
            sqlStrfinal=sqlStr+"limit ?,?";
            var para = [req.session.username,(page-1)*limit,limit*1.0];
        }
        else{
            var tag = req.query.searchParams.split(",")[0][1];
            if(tag==1){
                var manager = req.query.searchParams.split(",")[1];
                sqlStrfinal = sqlStr+"and m_id = ? "+"limit ?,?";
                var para = [req.session.username,manager,(page-1)*limit,limit];
            }else if(tag==2){
                var status = req.query.searchParams.split(",")[1];
                sqlStrfinal = sqlStr+"and status = ? "+"limit ?,?";
                var para = [req.session.username,status,(page-1)*limit,limit];
            }else if(tag==3){
                var type = req.query.searchParams.split(",")[1];
                sqlStrfinal = sqlStr+"and type = ? "+"limit ?,?";
                var para = [req.session.username,type,(page-1)*limit,limit];
            }
        }
        if(req.query.function==undefined)
            db.all(sqlStrfinal,para,function(err,rows){
                if(rows.length==0){
                    table = {"code":0,"msg":"","count":rows.length,"data":""};
                }else{
                    console.log(rows);
                    table = {"code":0,"msg":"","count":rows.length,"data":""};
                    for(var i=0;i<rows.length;i++){
                        if(rows[i].status=='1')
                            rows[i].status='已提交材料';
                        else if(rows[i].status=='2')
                            rows[i].status='已审核';
                        else if(rows[i].status=='0')
                            rows[i].status='未提交材料';

                        if(rows[i].score=='-1')
                            rows[i].score='未赋分';
                    }
                    table.data=rows;
                }
                res.status(200).json(table);
            } );
    }catch(ex){
        console.log(ex);
    }
});

module.exports = router;
